/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BLL;

import DAL.ConnectUnit;
import DAL.SQLConnect;
import DTO.Customer_DTO;
import DTO.NguoiQuenDTO;
import DTO.SoLuong_STK_DTO;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import javax.swing.JOptionPane;

/**
 *
 * @author Nguyen Phi Hung
 */
public class Customer_BLL {
    
    ConnectUnit connection;
    SQLConnect sql_connect=null;
    public Customer_BLL(){
        this.connection = DAL.DAL.getDAL();
        this.sql_connect=new SQLConnect("localhost","sa", " ", "SBM");
    }
    public ResultSet ExcuteQuery(String procedure,String sql) throws SQLException, Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setString(1, sql);
        return cs.executeQuery();
    
    }
    public boolean Insert_Update(String procedure,Customer_DTO info) throws Exception
    {
        CallableStatement cs=connection.GetCallableStatement(procedure);
        cs.setString(1, info.getCustomerID());
        cs.setString(2, info.getFullName());
        cs.setString(3, info.getIDNumber());
        cs.setString(4, info.getHomeAddress());
        cs.setString(5, info.getEmail());
        cs.setString(6, info.getPhone());
        return cs.execute();
        //return conn.Insert("Customer", customer);
    }
    //update 
    public boolean Delete(String procedure,String CustomerID) throws Exception
    {
        CallableStatement cs=connection.GetCallableStatement(procedure);
        cs.setString(1, CustomerID);
        cs.registerOutParameter(2, java.sql.Types.BOOLEAN);
        cs.execute();
        return cs.getBoolean(2);
    }
    public boolean KiemTraThongTin_KH(String procedure,String HoTen,String CMND) throws Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setString(1,HoTen);
        cs.setString(2,CMND);
        cs.registerOutParameter(3, java.sql.Types.BOOLEAN);
        cs.execute();
        boolean result=cs.getBoolean(3);
        if(result==true)
            return true;
        else
            return false;
        
    }
    //kiem tra so luong so tiet kiem
    public int SoLuong_STK(String procedure,String HoTen,String CMND) throws SQLException, Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setString(1,HoTen);
        cs.setString(2, CMND);
        cs.registerOutParameter(3,java.sql.Types.INTEGER);
        cs.execute();
        return cs.getInt(3);
    }
    //kiem tra so tien gui co duoc phep gui hay khong
    public boolean KiemTraSoTienGui(String procedure,float SoTienGui) throws Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setFloat(1, SoTienGui);
        cs.registerOutParameter(2, java.sql.Types.BOOLEAN);
        cs.execute();
        return cs.getBoolean(2);
    }
    public String Search(
            boolean bCustomerID,String CustomerID,
            boolean bFullName,String FullName,
            boolean bIDNumber,String IDNumber,
            boolean bHomeAddress,String HomeAddress,
            boolean bEmail,String Email,
            boolean bPhone,String Phone
            )
    {
        boolean isFirst=true;
        String sql="select * from customer ";
        if(bCustomerID)
        {
            sql+="where CustomerID like '%"+CustomerID+"%' ";
            isFirst=false;
        }
        //
        if(bFullName)
        {
             if (isFirst)
                {
                    sql += "where FullName like '%" + FullName + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and FullName like '%" + FullName + "%' ";
             }
        }
        //
         if(bIDNumber)
        {
             if (isFirst)
                {
                    sql += "where IDNumber like '%" + IDNumber + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and IDNumber like '%" + IDNumber + "%' ";
             }
        }
         // 
        if(bHomeAddress)
        {
             if (isFirst)
                {
                    sql += "where HomeAddress like '%" + HomeAddress + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and HomeAddress like '%" + HomeAddress + "%' ";
             }
        }
        //
        if(bEmail)
        {
             if (isFirst)
                {
                    sql += "where Email like '%" + Email + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and Email like '%" + Email + "%' ";
             }
        }
        //
        if(bPhone)
        {
             if (isFirst)
                {
                    sql += "where Phone like '%" + Phone + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and Phone like '%" + Phone + "%' ";
             }
        }
        return sql;
    }

    public static void main(String[] str) throws Exception
    {
        Customer_BLL customer_bll=new Customer_BLL();
        Customer_DTO info=new Customer_DTO();
        info.setCustomerID("");
        info.setFullName("sa");
        info.setIDNumber("122");
        info.setHomeAddress("sasa");
        info.setEmail("sas");
        info.setPhone("sasa");
        JOptionPane.showMessageDialog(null,customer_bll.Insert_Update("{call dbo.Insert_Customer(?,?,?,?,?,?)}",info));
    }
}

